IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAdvanceFundRequestInfoByProposalNumber]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAdvanceFundRequestInfoByProposalNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--GetAdvanceFundRequestInfoByProposalNumber '2007A004340'

CREATE PROCEDURE [dbo].[GetAdvanceFundRequestInfoByProposalNumber] 
(  
	@PropNumber	VARCHAR(50)
)  
AS 

SET NOCOUNT ON
BEGIN  
		    
			DECLARE @email VARCHAR(100)
		    EXECUTE @email = dbo.[GetServerEMail]

			SELECT af.foldernumber AS 'PropNumber', 
			adp.FwkDomainUserId AS 'PIId', 
			adpM.FwkDomainUserId AS 'ChiefId',
			adpO.FwkDomainUserId AS 'GAId',
			adpF.FwkDomainUserId AS 'DAId',
			adparf.FwkDomainUserId As 'DelegateId',
			--'182029' AS 'DelegateId',
			ISNULL(@email,eaPI.EmailAddress) AS 'PIEmailId',
			ISNULL(@email,eaS.EmailAddress) AS 'SubmitterEmailId',	
			ISNULL(@email,eaC.EmailAddress) AS 'ChiefEmailId',
			ISNULL(@email,eaD.EmailAddress) AS 'DelegateEmailId',
			ISNULL(@email,eaGA.EmailAddress) AS 'GAEmailId',
			ISNULL(@email,eaDA.EmailAddress) AS 'DAEmailId',
			af.OrganizationId As 'OrganizationId', 
			af.Title AS 'Title', 
			af.ShortTitle AS 'ShortTitle',
			af.SponsorName AS 'SponsorName', 
			af.PILastName + ', ' + af.PiFirstName AS 'PIName', 
			adpF.LastName + ', ' + adpF.FirstName AS 'DAName',
			adpO.LastName + ', ' + adpO.FirstName AS 'GAName',	
			adpM.LastName + ', ' + adpM.FirstName AS 'ChiefName',
			admS.LastName + ', ' + admS.FirstName AS 'SubmitterName',			
			af.Institution As 'Organization', 
			af.Department AS 'Department',
			af.ChiefCode AS 'COS',
			CONVERT(VARCHAR(10), af.ProjectStartDate,101) + ' - ' + CONVERT(VARCHAR(10), af.ProjectEndDate,101) AS 'PeriodDate',
			adv.CreatedBy,
			adv.SubmittedBy,
			awf.AdvanceFundRequestStatusTypeId AS 'Status',
			afst.[Name] AS 'StatusName',
			adv.FundNumber AS 'FundNumber',
			af.AgrAgreementType AS 'InstrumentType',
			NULL AS 'OriginatingSponsor'
			FROM  agragreement_fct af	
			LEFT OUTER JOIN dbo.AdvanceFundRequest adv ON adv.PropNumber = af.foldernumber 	
			LEFT OUTER JOIN dbo.AdvanceFundRequestWorkFlowStatus awf ON awf.AdvanceFundRequestId = adv.Id AND awf.AdvanceFundRequestStatusTypeId IN (SELECT TOP 1 AdvanceFundRequestStatusTypeId FROM dbo.AdvanceFundRequestWorkFlowStatus aaa WHERE aaa.AdvanceFundRequestId = adv.Id ORDER BY aaa.AdvanceFundRequestStatusTypeId DESC)
			LEFT OUTER JOIN dbo.AdvanceFundRequestStatusType afst ON afst.Id = awf.AdvanceFundRequestStatusTypeId
			INNER JOIN dbo.agragreementstatustype ast ON ast.id = af.statustypeid
			INNER JOIN dbo.agragreementstatustype ast1 ON ast1.Id = ast.mappedtostatus
			INNER JOIN dbo.FwkDomainOrganization fdo ON af.OrganizationId = fdo.Id
			INNER JOIN dbo.AdmPerson adp ON af.PrincipalInvestigatorId = adp.Id		
			LEFT OUTER JOIN dbo.AdmEmailAddress eaPI ON eaPI.AdmPersonId = adp.Id AND eaPI.AdmEmailAddressTypeId = 1 -- PI Email ID
			LEFT OUTER JOIN dbo.AdmResearchFinanceInfo afi ON afi.FwkdomainorganizationId = fdo.Id AND afi.RoleId = 1
			LEFT OUTER JOIN dbo.AdmPerson adpF ON adpF.Id = afi.AdmPersonId
			LEFT OUTER JOIN dbo.AdmEmailAddress eaDA ON eaDA.AdmPersonId = afi.AdmPersonId AND eaDA.AdmEmailAddressTypeId = 1 -- DA Email ID
			LEFT OUTER JOIN dbo.AdmResearchFinanceInfo arfC ON arfC.FwkDomainOrganizationId = fdo.Id AND arfC.RoleId = 11
			LEFT OUTER JOIN dbo.AdmPerson adpM ON adpM.Id = arfC.AdmPersonId
			LEFT OUTER JOIN dbo.AdmEmailAddress eaC ON eaC.AdmPersonId = arfC.AdmPersonId AND eaC.AdmEmailAddressTypeId = 1 -- Chief Email ID
			LEFT OUTER JOIN dbo.AdmPerson admS ON admS.FwkDomainUserId = adv.SubmittedBy
			LEFT OUTER JOIN dbo.AdmEmailAddress eaS ON eaS.AdmPersonId = admS.Id AND eaS.AdmEmailAddressTypeId = 1 -- Submitter Email ID
			LEFT OUTER JOIN dbo.AdmResearchFinanceInfo arf ON arf.FwkDomainOrganizationId = fdo.Id AND arf.RoleId = 7
			LEFT OUTER JOIN dbo.AdmPerson adparf ON adparf.Id = arf.AdmPersonId
			LEFT OUTER JOIN dbo.AdmEmailAddress eaD ON eaD.AdmPersonId = arf.AdmPersonId AND eaD.AdmEmailAddressTypeId = 1 -- Delegate Email ID
			LEFT OUTER JOIN dbo.AdmResearchFinanceInfo arfGA ON arfGA.FwkDomainOrganizationId = fdo.Id AND arfGA.RoleId = 3
			LEFT OUTER JOIN dbo.AdmPerson adpO ON adpO.Id = arfGA.AdmPersonId
			LEFT OUTER JOIN dbo.AdmEmailAddress eaGA ON eaGA.AdmPersonId = arfGA.AdmPersonId AND eaGA.AdmEmailAddressTypeId = 1 -- GA Email ID

			WHERE af.foldernumber = @PropNumber
		  --PRINT(@OrderBy)

END


GO